﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

namespace MyTech.DataAccess.Common
{
    public abstract class DataAccessSQLBase
    {
        protected string connectionString = string.Empty;
        protected IDbConnection _connection = null;        
       
        protected virtual void Connect()
        {
            if (null != _connection)
            {
                _connection.Open();
            }
        }

        protected virtual void DisConnect()
        {
            if (null != _connection)
            {
                _connection.Close();
            }
        }

        protected virtual int? ExecuteNonQuery(IDbCommand command)
        {
            int? response = null;
            IDbTransaction transsaction = null;
            try
            {
                Connect();
                if (null != _connection)
                {
                    transsaction = _connection.BeginTransaction();
                    command.Connection = _connection;
                    command.Transaction = transsaction;
                    response = command.ExecuteNonQuery();
                    transsaction.Commit();
                }
            }
            catch (Exception)
            {
                if (null != transsaction)
                {
                    transsaction.Rollback();
                }
            }
            finally
            {
                DisConnect();
            }

            return response;
        }

        protected DataSet ExecuteReader(IDbCommand command)
        {
            DataSet response = null;
            try
            {
                Connect();
                if (null != _connection)
                {
                    command.Connection = _connection;                  
                    var dataTable = new DataTable();
                    dataTable.Load(command.ExecuteReader());
                    response = dataTable.DataSet;
                }
            }
            finally
            {
                DisConnect();
            }

            return response;
        }


        protected virtual object ExecuteScalar(IDbCommand command)
        {
            object response = null;
            IDbTransaction transsaction = null;
            try
            {
                Connect();
                if (null != _connection)
                {
                    transsaction = _connection.BeginTransaction();
                    command.Connection = _connection;
                    command.Transaction = transsaction;
                    response = command.ExecuteScalar();
                    transsaction.Commit();
                }
            }
            catch (Exception)
            {
                if (null != transsaction)
                {
                    transsaction.Rollback();
                }
            }
            finally
            {
                DisConnect();
            }

            return response;
        }
    }
}
